package data;


import java.sql.*;


public class InterfaceBD {

    /** Inicia o driver, abre a ligação e cria um statement */

    public Connection con;
    public String userName;
    public String password;
    public final String url = "jdbc:mysql://82.103.138.122/teo_dss";
    public Statement st;
    public ResultSet rs;
    public int res;

    public InterfaceBD(){

        try {
            Class.forName ("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Problemas na inicializaçao do driver \n");
        }
        this.userName= "tiago";
        this.password= "98u+zz1fjw5j";
        try {
            this.setCon(DriverManager.getConnection(url, userName, password));
            setSt(getCon().createStatement());
        } catch (SQLException e) {
            System.out.println("Problemas no acesso a Base de Dados\n");
        }
    }

    public void criaTabelas() throws SQLException {

        String[] query = new String[13];

        query[0] = "CREATE TABLE Utilizador (cod_u Int PRIMARY KEY,nomeu VARCHAR(40), password VARCHAR(20) NOT NULL, tipo int, localidadeu VARCHAR(40),factelec Varchar(5),debdir VARCHAR(5), dataadesaou DATE)";
        query[1] = "CREATE TABLE Historico(cod_hist Int PRIMARY KEY, cod_uh Int REFERENCES Utilizador(cod_u) ON UPDATE CASCADE ON DELETE CASCADE, datah DATE, desch VARCHAR(500))";
        query[2] = "CREATE TABLE Canal(cod_canal Int PRIMARY KEY, nomec VARCHAR(20), desch VARCHAR(500) )";
        query[3] = "CREATE TABLE Pacote(cod_pacote Int PRIMARY KEY, nomep VARCHAR(40), descpa VARCHAR(500), precop DOUBLE )";
        query[4] = "CREATE TABLE PacotesSubscritos(cod_utps Int REFERENCES Utilizador(cod_u) ON UPDATE CASCADE ON DELETE CASCADE, cod_pacoteps Int REFERENCES Pacote(cod_pacote) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY(cod_utps,cod_pacoteps))";
        query[5] = "CREATE TABLE Pedidos(cod_pedido Int PRIMARY KEY, cod_utp Int REFERENCES Utilizador(cod_u) ON UPDATE CASCADE ON DELETE CASCADE, descpe VARCHAR(500), datape DATE, localped VARCHAR(40))";
        query[6] = "CREATE TABLE Servico(cod_servico Int PRIMARY KEY, nomeserv VARCHAR(30), descserv VARCHAR(500), precoserv DOUBLE)";
        query[7] = "CREATE TABLE ServicosSubscritos(cod_servss Int REFERENCES Servico(cod_servico) ON UPDATE CASCADE ON DELETE CASCADE, cod_uss Int REFERENCES Utilizador(cod_u) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY(cod_servss,cod_uss))";
        query[8] = "CREATE TABLE Localidade(local VARCHAR(40) PRIMARY KEY)";
        query[9] = "CREATE TABLE Reclamacao(cod_reclamacao Int PRIMARY KEY, cod_urecla Int REFERENCES Utilizador(cod_u) ON UPDATE CASCADE ON DELETE CASCADE, descrecla VARCHAR(500),datarec DATE)";
        query[10] = "CREATE TABLE ConstPacote(cod_canalcp Int REFERENCES Canal(cod_canal) ON UPDATE CASCADE ON DELETE CASCADE, cod_pacotecp Int REFERENCES Pacote(cod_pacote) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY(cod_canalcp,cod_pacotecp))";
        query[11] = "INSERT INTO Localidade VALUES('Braga')";
        query[12] = "INSERT INTO Utilizador VALUES(1000,'root','admin',4,'Braga','','',sysDate())";
        //query[12] = "INSERT INTO Utilizador VALUES(2,'Felizberto','teo',0,'Braga','','',sysDate())";

        for(int i =0; i<query.length;i++){

                int rs = getSt().executeUpdate(query[i]);

        }
    }

    public void eliminaTabelas() throws SQLException {
        String[] query = new String[10];
        query[0] = "DROP TABLE Localidade";
        query[1] = "DROP TABLE Reclamacao";
        query[2] = "DROP TABLE ServicosSubscritos";
        query[3] = "DROP TABLE PacotesSubscritos";
        query[5] = "DROP TABLE ConstPacote";
        query[4] = "DROP TABLE Servico";
        query[5] = "DROP TABLE Pedidos";
        query[6] = "DROP TABLE Historico";
        query[7] = "DROP Table Pacote";
        query[8] = "DROP TABLE Canal";
        query[9] = "DROP TABLE Utilizador";

    for(int i =0; i<query.length;i++){

                int rs = getSt().executeUpdate(query[i]);

        }
    }

    public String testaPergunta() throws SQLException {
        String query = "Select * from Pacote";
        rs = st.executeQuery(query);
        //rs.next();
        return rs.getString("nomep");


    }



    public Connection getCon() {
        return con;
    }

    public void setCon(Connection con) {
        this.con = con;
    }

    public Statement getSt() {
        return st;
    }

    public void setSt(Statement st) {
        this.st = st;
    }

    public ResultSet getRs() {
        return rs;
    }

    public void setRs(ResultSet rs) {
        this.rs = rs;
    }

    public int getRes() {
        return res;
    }

    public void setRes(int res) {
        this.res = res;
    }
     public String insereUtilizador(int codU, String nome, String password, int tipo, String morada,String data, String debito, String factura){
        return "Insert INTO Utilizador Values("+codU+",'"+nome+"','"+password+"',"+tipo+",'"+morada+"','"+factura+"','"+debito+"','"+data+"')";


    }

}
